package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.OrderDrug;
import sy.pageModel.Partner;
import sy.service.PartnerServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-06-29.
 */
@Service
public class PartnerServiceImpl implements PartnerServiceI {

    @Autowired
    private JdbcTemplate template;
    @Override
    public DataGrid detagrid(Partner partner) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from u_dept where 1=1  ";
        sql = addWhere(partner, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "SELECT *from u_dept ud,u_dept_type udt where ud.typeid=udt.id ";
        sql = addWhere(partner, sql);
        sql = addOrderBy(partner, sql);
        sql = addLimit(partner, sql);
        final List<Partner> partnerList = new ArrayList<Partner>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Partner p=new Partner();
                p.setId(rs.getString("ud.id"));
                p.setName(rs.getString("ud.depts"));
                p.setDeptTypeId(rs.getString("udt.id"));
                p.setDeptTypeName(rs.getString("udt.names"));
                p.setAddress(rs.getString("addre"));
                p.setUrl(rs.getString("website"));
                p.setPic(rs.getString("pic"));
                p.setIsShow(rs.getString("is_show"));
                p.setOrder(rs.getString("orderid"));
                p.setCreateTimeStart(rs.getString("date_time"));
                partnerList.add(p);
            }});
        dg.setRows(partnerList);
        return dg;
    }

    @Override
    public List<BaseEntity> findTypeList() {
        String sql="select *from u_dept_type";
        final List<BaseEntity> list=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString("id"));
                entity.setName(rs.getString("names"));
                list.add(entity);
            }
        });
        return list;
    }

    @Override
    public List<BaseEntity> findDeptList() {
        String sql="select *from u_dept";
        final List<BaseEntity> list=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString("id"));
                entity.setName(rs.getString("depts"));
                list.add(entity);
            }
        });
        return list;
    }

    @Override
    @Transactional
    public int addPartnerService(Partner partner) throws Exception{
        String sql="INSERT INTO u_dept(id, typeid, depts,addre,is_show, website, pic, orderid, is_website, date_time) VALUES (?,?,?,?,?,?,?,?,?,?)";
        int rows=template.update(sql,new Object[]{partner.getId(),partner.getDeptTypeId(),partner.getName(),partner.getAddress(),partner.getIsShow(),partner.getUrl(),partner.getPic(),partner.getOrder(),partner.getRemarkUrl(),StringUtil.getDate()});
        return rows;
    }

    @Override
    @Transactional
    public int editPartnerService(Partner partner)throws Exception {
        String sql="UPDATE u_dept SET typeid=?, depts=?,addre=?,is_show=?, website=?, pic=?, orderid=?, is_website=? WHERE id=?";
        int rows=template.update(sql,new Object[]{partner.getDeptTypeId(),partner.getName(),partner.getAddress(),partner.getIsShow(),partner.getUrl(),partner.getPic(),partner.getOrder(),partner.getRemarkUrl(),partner.getId()});
        return rows;
    }

    @Override
    @Transactional
    public int deletePartnerService(Partner partner)throws Exception {
        String sql="DELETE FROM u_dept where id=?";
        int rows=0;
        String ids[]=partner.getIds().split(",");
        for (int i = 0; i <ids.length ; i++) {
            rows=template.update(sql,new Object[]{ids[i]});
        }
        return rows;
    }

    private String addLimit(Partner entity, String sql) {
        int fromRow = (entity.getPage() - 1) * entity.getRows();
        sql += " limit " + fromRow + "," + entity.getRows();
        return sql;
    }

    private String addWhere(Partner partner, String sql) {
        if(!StringUtil.isNullOrEmpty(partner.getDeptTypeId())) {
            sql += " and typeid like '%" + partner.getDeptTypeId() + "%'";
        }
        if(!StringUtil.isNullOrEmpty(partner.getName())) {
            sql += " and depts like '%" + partner.getName() + "%'";
        }
        if(!StringUtil.isNullOrEmpty(partner.getCreateTimeStart())) {
            sql += " and date_time >= '" + partner.getCreateTimeStart() + "'";
        }
        if(!StringUtil.isNullOrEmpty(partner.getCreateTimeEnd())) {
            sql += " and date_time <= '" + partner.getCreateTimeEnd() + "'";
        }
        return sql;
    }

    private String addOrderBy(Partner partner, String sql) {
        if (partner.getSort() != null) {
            if("name".equals(partner.getSort())) partner.setSort("ud.depts");
            if("deptTypeId".equals(partner.getSort())) partner.setSort("udt.names");
            if("url".equals(partner.getSort())) partner.setSort("ud.website");
            if("isShow".equals(partner.getSort())) partner.setSort("ud.is_show");
            if("id".equals(partner.getSort())) partner.setSort("ud.id");
            if("createTimeStart".equals(partner.getSort())) partner.setSort("ud.date_time");
            sql += " order by " + partner.getSort() + " " + partner.getOrder();
        }
        return sql;
    }
}
